package com.itfreer.data.metadata;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;

import org.hibernate.internal.SessionImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

/**
 * 获取元数据信息
 */
@Component
public class MetadataImp implements IMetadata {

	@Autowired
	protected EntityManager em;
	@Autowired
	protected PlatformTransactionManager transactionManager;

	/**
	 * 获取数据库中的表名称与视图名称
	 * 
	 * @return
	 */
	@Override
	public List<String> getTablesAndViews() throws Exception {
		// 配置事务策略
		DefaultTransactionDefinition def = new DefaultTransactionDefinition();
		def.setName("planOne-transaction");
		def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
		// 设置状态点
		TransactionStatus status = transactionManager.getTransaction(def);
		List<String> list = new ArrayList<String>();
		try {
			Connection connection = em.unwrap(SessionImpl.class).connection();
			DatabaseMetaData metaData = connection.getMetaData();
			ResultSet rs = null;
			rs = metaData.getTables(null, null, null, new String[] { "TABLE", "VIEW" });
			while (rs.next()) {
				String tableName = rs.getString("TABLE_NAME");
				list.add(tableName);
			}
		} catch (Exception e) {
			transactionManager.rollback(status);
			throw e;
		}
		transactionManager.commit(status);
		return list;
	}

	/**
	 * 利用表名和数据库用户名查询出该表对应的字段类型
	 * 
	 * @param tableName
	 *            表名
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Field> getFields(String tableName) throws Exception {
		// 配置事务策略
		DefaultTransactionDefinition def = new DefaultTransactionDefinition();
		def.setName("planOne-transaction");
		def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
		// 设置状态点
		TransactionStatus status = transactionManager.getTransaction(def);
		List<Field> list = new ArrayList<Field>();
		try {
			Connection connection = em.unwrap(SessionImpl.class).connection();
			DatabaseMetaData metaData = connection.getMetaData();
			ResultSet rs = null;
			rs = metaData.getColumns(null, null, tableName, null);
			while (rs.next()) {
				int ordinalPosition = rs.getInt("ORDINAL_POSITION"); // 表中列的索引（从1开始）
				String columnName = rs.getString("COLUMN_NAME");// 列名
				String typeName = rs.getString("TYPE_NAME");// 字段类型名称(例如：VACHAR2)
				int columnSize = rs.getInt("COLUMN_SIZE"); // 列大小
				int decimalDigits = rs.getInt("DECIMAL_DIGITS"); // 小数位数
				String remarks = rs.getString("REMARKS"); // 列描述
				String columnDef = rs.getString("COLUMN_DEF"); // 默认值

				Field e = new Field();
				e.setId(ordinalPosition);
				e.setFieldName(columnName);
				e.setFieldAliasName(remarks);
				e.setFieldType(typeName);
				e.setMaxLen(columnSize);
				e.setDecimaldigits(decimalDigits);
				e.setDefValue(columnDef);
				list.add(e);
			}
		} catch (Exception e) {
			transactionManager.rollback(status);
			throw e;
		}
		transactionManager.commit(status);
		return list;
	}

	/**
	 * 利用表名和数据库用户名查询出该表对应的字段类型
	 * 
	 * @param driver
	 *            驱动
	 * @param url
	 *            url信息
	 * @param username
	 *            用户名
	 * @param password
	 *            密码
	 * @param tableName
	 *            表名
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Field> getFields(String driver, String url, String username, String password, String tableName)
			throws Exception {
		Connection connection = getConnection(driver, url, username, password);
		DatabaseMetaData metaData = connection.getMetaData();

		ResultSet rs = null;
		rs = metaData.getColumns(null, null, tableName, null);
		List<Field> list = new ArrayList<Field>();
		while (rs.next()) {
			int ordinalPosition = rs.getInt("ORDINAL_POSITION"); // 表中列的索引（从1开始）
			String columnName = rs.getString("COLUMN_NAME");// 列名
			String typeName = rs.getString("TYPE_NAME");// 字段类型名称(例如：VACHAR2)
			int columnSize = rs.getInt("COLUMN_SIZE"); // 列大小
			int decimalDigits = rs.getInt("DECIMAL_DIGITS"); // 小数位数
			String remarks = rs.getString("REMARKS"); // 列描述
			String columnDef = rs.getString("COLUMN_DEF"); // 默认值

			Field e = new Field();
			e.setId(ordinalPosition);
			e.setFieldName(columnName);
			e.setFieldAliasName(remarks);
			e.setFieldType(typeName);
			e.setMaxLen(columnSize);
			e.setDecimaldigits(decimalDigits);
			e.setDefValue(columnDef);
			list.add(e);
		}
		return list;
	}

	private static Connection getConnection(String driver, String url, String username, String password) {
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, username, password);
			return conn;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
}
